﻿using System;
using System.Data;
using System.Globalization;
using System.Text.RegularExpressions;
using DAO;
using System.IO;

namespace BUS
{
    public class CommonControl
    {
        DAO.Connection _conn;
        static string sErr = "";
        public CommonControl()
        {
            _conn = new DAO.Connection();
        }
        public DateTime getDateSys()
        {
            return _conn.GetDateSys();
        }
        public static int ConvertDateToInt(DateTime? dt)
        {
            if (dt == null || dt.Value.Year == 1)
            {
                return 19000101;
            }
            return dt.Value.Year * 10000 + dt.Value.Month * 100 + dt.Value.Day;
        }
        //public static string GetParseExpressionDate(string columnDate, string dtb)
        //{
        //    DBAControl dba_ctr = new DBAControl();
        //    DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);
        //    string result = "SUBSTRING(CONVERT(" + columnDate + ",System.String),7,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),5,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),1,4)";
        //    string fm = dba_info.DATE_FORMAT;  // lay tu database

        //    switch (fm)
        //    {
        //        case "A":
        //            result = "SUBSTRING(CONVERT(" + columnDate + ",System.String),5,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),7,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),1,4)";
        //            break;
        //        case "B":
        //            result = "SUBSTRING(CONVERT(" + columnDate + ",System.String),7,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),5,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),1,4)";
        //            break;
        //        case "C":
        //            result = "SUBSTRING(CONVERT(" + columnDate + ",System.String),1,4)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),5,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),7,2)";
        //            break;
        //    }
        //    return result;
        //}
        public static string GetParseExpressionPeriod(string columnDate)
        {
            string result = "SUBSTRING(CONVERT(" + columnDate + ",System.String),5,3)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),1,4)";
            return result;
        }

        public static DateTime? ConvertIntToDate(int dt)
        {
            if (dt == 0 || dt == -1 || dt == 19000101)
            {
                return new DateTime(1900, 1, 1);
            }
            DateTime? result = null;
            try
            {
                //result = new DateTime(
                result = new DateTime(dt / 10000, (dt % 10000) / 100, ((dt % 10000) % 100));
            }
            catch { }
            return result;
        }
        public static String ConvertIntToString(int dt)
        {
            if (dt == 0 || dt == -1 || dt == 19000101)
            {
                return "";
            }
            String mon = Convert.ToString((dt % 10000) / 100);
            if (mon.Length == 1) mon = "0" + mon;
            String dd = Convert.ToString((dt % 10000) % 100);
            if (dd.Length == 1) dd = "0" + dd;
            return (dt / 10000 + "/" + mon + "/" + dd);
        }
        /*
         public static String ConvertIntToDateString(int dt, string dtb)
        {
            if (dt == 0 || dt == -1 || dt == 19000101)
            {
                return "";
            }
            DateTime? result = null;
            try
            {
                //result = new DateTime(
                result = new DateTime(dt / 10000, (dt % 10000) / 100, ((dt % 10000) % 100));
                string kq = Get_Date_Format(dtb);
                string day = "00" + result.Value.Day.ToString();
                day = day.Substring(day.Length - 2);
                string month = "00" + result.Value.Month.ToString();
                month = month.Substring(month.Length - 2);
                string year = "0000" + result.Value.Year.ToString();
                year = year.Substring(year.Length - 4);
                kq = kq.Replace("dd", day).Replace("MM", month).Replace("yyyy", year);
                return kq;// result.Value.ToString(Get_Date_Format(dtb));
            }
            catch { }
            return "";
        }
        public DataTable SelectData(string fields, string table, ref string sErr)
        {
            return _conn.SelectData(fields, table, ref sErr);
        }
        public static String Get_Date_Format(String dtb)
        {
            string kq = "";
            string sErr = "";
            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);

            string fm = dba_info.DATE_FORMAT;  // lay tu database

            switch (fm)
            {
                case "A":
                    kq = "MM/dd/yyyy";
                    break;
                case "B":
                    kq = "dd/MM/yyyy";
                    break;
                case "C":
                    kq = "yyyy/MM/dd";
                    break;
            }

            return kq;

        }

        public static String Format_Number(Decimal temp, string dtb)
        {
            // lay tu database
            String kq = "";
            string sErr = "";

            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);

            string dec = dba_info.DECIMAL_SEPERATOR;
            string thou = dba_info.THOUSAND_SEPERATOR;
            string fm = thou + dec;
            //   string chuoi = new string('0',Convert.ToInt32(dba_info.DECIMAL_PLACES_SUNBUSINESS));
            string num_dec = "N" + dba_info.DECIMAL_PLACES_SUNBUSINESS;
            switch (fm)
            {
                case ".,":
                    System.Globalization.CultureInfo a = new System.Globalization.CultureInfo("de-DE");
                    kq = temp.ToString(num_dec, a);
                    break;
                case ",.":
                    System.Globalization.CultureInfo b = new System.Globalization.CultureInfo("en-US");
                    kq = temp.ToString(num_dec, b);
                    break;
            }

            return kq;
        }
        public static String Format_NumberAcc(Decimal temp, string dtb)
        {
            // lay tu database
            String kq = "";
            string sErr = "";

            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);

            string dec = dba_info.DECIMAL_SEPERATOR;
            string thou = dba_info.THOUSAND_SEPERATOR;
            string fm = thou + dec;
            //   string chuoi = new string('0',Convert.ToInt32(dba_info.DECIMAL_PLACES_SUNBUSINESS));
            string num_dec = "N" + dba_info.DECIMAL_PLACES_SUNACCOUNT;
            switch (fm)
            {
                case ".,":
                    System.Globalization.CultureInfo a = new System.Globalization.CultureInfo("de-DE");
                    kq = temp.ToString(num_dec, a);
                    break;
                case ",.":
                    System.Globalization.CultureInfo b = new System.Globalization.CultureInfo("en-US");
                    kq = temp.ToString(num_dec, b);
                    break;
            }

            return kq;
        }
        public static string GetCultureString(string dtb)
        {
            String kq = "";
            string sErr = "";

            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);

            string dec = dba_info.DECIMAL_SEPERATOR;
            string thou = dba_info.THOUSAND_SEPERATOR;
            string fm = thou + dec;
            if (fm == ".,")
                return "de-DE";
            else if (fm == ",.")
                return "en-US";
            return kq;

        }
        public static String GetNumberFormat(string dtb, string currency)
        {
            string sErr = "";
            String kq = "";
            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);
            string dec = dba_info.DECIMAL_SEPERATOR;
            string thou = dba_info.THOUSAND_SEPERATOR;
            int icountdec = dba_info.DECIMAL_PLACES_SUNBUSINESS == "" ? 0 : Convert.ToInt32(dba_info.DECIMAL_PLACES_SUNBUSINESS);
            // kq = "###" + thou + "###" + dec + new String('#', icountdec) + "" + currency + "";
            kq = "N" + icountdec;
            return kq;
        }
        public static String GetNumberFormatA(string dtb)
        {
            string sErr = "";
            String kq = "";
            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);
            string dec = dba_info.DECIMAL_SEPERATOR;
            string thou = dba_info.THOUSAND_SEPERATOR;
            int icountdec = dba_info.DECIMAL_PLACES_SUNACCOUNT == "" ? 0 : Convert.ToInt32(dba_info.DECIMAL_PLACES_SUNACCOUNT);
            // kq = "###" + thou + "###" + dec + new String('#', icountdec) + "" + currency + "";
            kq = "N" + icountdec;
            return kq;
        }
        public static Decimal Format_StringToNumber(string temp, string dtb)
        {
            // lay tu database Regex.Matches(\d 
            Decimal kq = 0;
            if (Regex.IsMatch(temp, @"\d+"))
            {


                string sErr = "";

                DBAControl dba_ctr = new DBAControl();
                DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);

                string dec = dba_info.DECIMAL_SEPERATOR;
                string thou = dba_info.THOUSAND_SEPERATOR;
                string fm = thou + dec;
                //   string chuoi = new string('0',Convert.ToInt32(dba_info.DECIMAL_PLACES_SUNBUSINESS));
                //      string num_dec = "N" + dba_info.DECIMAL_PLACES_SUNBUSINESS;
                switch (fm)
                {
                    case ".,":
                        System.Globalization.CultureInfo a = new System.Globalization.CultureInfo("de-DE");
                        kq = Convert.ToDecimal(temp, a);
                        break;
                    case ",.":
                        System.Globalization.CultureInfo b = new System.Globalization.CultureInfo("en-US");
                        kq = Convert.ToDecimal(temp, b);
                        break;
                }
            }
            return kq;
        }

        public static Decimal Round(Decimal obj, String dtb)
        {
            DBAControl control = new DBAControl();
            DTO.DBAInfo dba_info = control.Get(dtb, ref sErr);
            obj = Decimal.Round(obj, Convert.ToInt32(dba_info.DECIMAL_PLACES_SUNBUSINESS));
            return obj;
        }
        */
        public static String GetPeriodString(int p)
        {
            //Regex.IsMatch(p, @"^[0-9]{2}[1-9]{1}/[1-9]{1}[0-9]{3}$")

            if (p.ToString().Length == 7)
            {
                string result = p.ToString();
                return result.Substring(4, 3) + "/" + result.Substring(0, 4);
            }
            return "";
        }

        public static int ConvertStringToPeriod(string p)
        {
            if (Regex.IsMatch(p, @"^[0-9]{2}[1-9]{1}/[1-9]{1}[0-9]{3}$"))
            {
                string period = (p.Split('/'))[1] + (p.Split('/'))[0];
                return Convert.ToInt32(period);
            }
            return 1900001;
        }

        public static int ConvertDateToPeriodInt(DateTime? dateTime)
        {
            if (dateTime == null)
                return 1900001;
            return dateTime.Value.Year * 1000 + dateTime.Value.Month;
        }
        public IDataReader executeQuery(string sqlString)
        {
            _conn.connect();
            IDataReader kq = _conn.executeQuery(sqlString);
            _conn.disconnect();
            return kq;
        }
        public DataTable executeSelectQuery(string sqlString)
        {
            return _conn.executeSelectQuery(sqlString);
        }

        public void executeNonQuery(string sqlString)
        {
            _conn.connect();
            _conn.executeNonQuery(sqlString);
            _conn.disconnect();
        }

        public object executeScalar(string sqlString)
        {
            _conn.connect();
            object kq = _conn.executeScalar(sqlString);
            _conn.disconnect();
            return kq;
        }
        /*
        public DataTable GetTableStruct(string tableName)
        {
            DataTable dt = new DataTable();
            switch (tableName)
            {
                case "AND": BUS.ANDControl ctrAND = new ANDControl(); dt = ctrAND.ToTransferInStruct(); break;
                case "DBA": BUS.DBAControl ctrDBA = new DBAControl(); dt = ctrDBA.ToTransferInStruct(); break;
                case "LIST_QD": BUS.LIST_QDControl ctrLIST_QD = new LIST_QDControl(); dt = ctrLIST_QD.ToTransferInStruct(); break;
                case "POD": BUS.PODControl ctrPOD = new PODControl(); dt = ctrPOD.ToTransferInStruct(); break;
                case "POG": BUS.POGControl ctrPOG = new POGControl(); dt = ctrPOG.ToTransferInStruct(); break;
                case "POP": BUS.POPControl ctrPOP = new POPControl(); dt = ctrPOP.ToTransferInStruct(); break;
                case "RTD": BUS.RTDControl ctrRTD = new RTDControl(); dt = ctrRTD.ToTransferInStruct(); break;
                case "SALFMSC": BUS.SALFMSCControl ctrSALFMSC = new SALFMSCControl(); dt = ctrSALFMSC.ToTransferInStruct(); break;
                case "SSRFANV": BUS.SSRFANVControl ctrSSRFANV = new SSRFANVControl(); dt = ctrSSRFANV.ToTransferInStruct(); break;
                case "SSRFCND": BUS.SSRFCNDControl ctrSSRFCND = new SSRFCNDControl(); dt = ctrSSRFCND.ToTransferInStruct(); break;
                case "SSRFCNV": BUS.SSRFCNVControl ctrSSRFCNV = new SSRFCNVControl(); dt = ctrSSRFCNV.ToTransferInStruct(); break;
                case "SSRFCVD": BUS.SSRFCVDControl ctrSSRFCVD = new SSRFCVDControl(); dt = ctrSSRFCVD.ToTransferInStruct(); break;
            }
            return dt;
        }
        public DataTable GetAllDataTable(string tableName, string dtb, string from, string to, ref string sErr)
        {
            DataTable dt = new DataTable();
            switch (tableName)
            {
                case "AND": BUS.ANDControl ctrAND = new ANDControl(); return ctrAND.GetTransferOut(dtb, from, to, ref sErr);
                case "DBA": BUS.DBAControl ctrDBA = new DBAControl(); return ctrDBA.GetTransferOut(ref sErr);
                case "LIST_QD": BUS.LIST_QDControl ctrLIST_QD = new LIST_QDControl(); return ctrLIST_QD.GetTransferOut(dtb, from, to, ref sErr);
                case "POD": BUS.PODControl ctrPOD = new PODControl(); return ctrPOD.GetTransferOut(dtb, from, to, ref sErr);
                case "POG": BUS.POGControl ctrPOG = new POGControl(); return ctrPOG.GetTransferOut(dtb, from, to, ref sErr);
                case "POP": BUS.POPControl ctrPOP = new POPControl(); return ctrPOP.GetTransferOut(dtb, from, to, ref sErr);
                case "RTD": BUS.RTDControl ctrRTD = new RTDControl(); return ctrRTD.GetTransferOut(dtb, from, to, ref sErr);
                case "SALFMSC": BUS.SALFMSCControl ctrSALFMSC = new SALFMSCControl(); return ctrSALFMSC.GetTransferOut(dtb, from, to, ref sErr);
                case "ANV": BUS.SSRFANVControl ctrSSRFANV = new SSRFANVControl(); return ctrSSRFANV.GetTransferOut(dtb, from, to, ref sErr);
                case "CND": BUS.SSRFCNDControl ctrSSRFCND = new SSRFCNDControl(); return ctrSSRFCND.GetTransferOut(dtb, from, to, ref sErr);
                case "CNV": BUS.SSRFCNVControl ctrSSRFCNV = new SSRFCNVControl(); return ctrSSRFCNV.GetTransferOut(dtb, from, to, ref sErr);
                case "CVD": BUS.SSRFCVDControl ctrSSRFCVD = new SSRFCVDControl(); return ctrSSRFCVD.GetTransferOut(dtb, from, to, ref sErr);
                case "MMS": BUS.MMS_MEMBERControl ctrMMS = new MMS_MEMBERControl(); return ctrMMS.GetTransferOut(dtb, from, to, ref sErr);
            }

            return dt;
        }

        public string TransferInData(DataRow row, string tableName)
        {
            try
            {
                switch (tableName)
                {
                    case "AND": BUS.ANDControl ctrAND = new ANDControl(); return ctrAND.TransferIn(row);
                    case "DBA": BUS.DBAControl ctrDBA = new DBAControl(); return ctrDBA.TransferIn(row);
                    case "LIST_QD": BUS.LIST_QDControl ctrLIST_QD = new LIST_QDControl(); return ctrLIST_QD.TransferIn(row);
                    case "POD": BUS.PODControl ctrPOD = new PODControl(); return ctrPOD.TransferIn(row);
                    case "POG": BUS.POGControl ctrPOG = new POGControl(); return ctrPOG.TransferIn(row);
                    case "POP": BUS.POPControl ctrPOP = new POPControl(); return ctrPOP.TransferIn(row);
                    case "RTD": BUS.RTDControl ctrRTD = new RTDControl(); return ctrRTD.TransferIn(row);
                    case "SALFMSC": BUS.SALFMSCControl ctrSALFMSC = new SALFMSCControl(); return ctrSALFMSC.TransferIn(row);
                    case "ANV": BUS.SSRFANVControl ctrSSRFANV = new SSRFANVControl(); return ctrSSRFANV.TransferIn(row);
                    case "CND": BUS.SSRFCNDControl ctrSSRFCND = new SSRFCNDControl(); return ctrSSRFCND.TransferIn(row);
                    case "CNV": BUS.SSRFCNVControl ctrSSRFCNV = new SSRFCNVControl(); return ctrSSRFCNV.TransferIn(row);
                    case "CVD": BUS.SSRFCVDControl ctrSSRFCVD = new SSRFCVDControl(); return ctrSSRFCVD.TransferIn(row);
                    case "MMS": BUS.MMS_MEMBERControl ctrMMS = new MMS_MEMBERControl(); return ctrMMS.TransferIn(row);
                }
                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public DataTable ValidatedDataTransferIn(DataTable dt, string tableName)
        {
            DataTable dtStruct = GetTableStruct(tableName);
            DataColumn colValidated = new DataColumn("tmp_Validated", typeof(bool));
            dt.Columns.Add(colValidated);
            foreach (DataRow row in dt.Rows)
            {
                bool flag = true;
                foreach (DataColumn col in dtStruct.Columns)
                {
                    if (flag)
                        switch (col.DataType.ToString())
                        {
                            case "System.Decimal":
                                Decimal result;
                                flag = Decimal.TryParse(row[col.ColumnName].ToString(), out result);
                                break;
                            case "System.Int16":
                                Int16 resultInt16;
                                flag = Int16.TryParse(row[col.ColumnName].ToString(), out resultInt16);
                                break;
                            case "System.Int32":
                                Int32 resultInt32;
                                flag = Int32.TryParse(row[col.ColumnName].ToString(), out resultInt32);
                                break;
                            case "System.Int64":
                                Int64 resultInt64;
                                flag = Int64.TryParse(row[col.ColumnName].ToString(), out resultInt64);
                                break;
                            case "System.Boolean":
                                Boolean resultBoolean;
                                flag = Boolean.TryParse(row[col.ColumnName].ToString(), out resultBoolean);
                                break;
                        }
                    else
                        break;
                }
                row["tmp_Validated"] = flag;
            }
            return dt;
        }

        public static int ConvertDateStringToIn(string date, String dtb)
        {
            DBAControl dba_ctr = new DBAControl();
            DTO.DBAInfo dba_info = dba_ctr.Get(dtb, ref sErr);
            try
            {
                string[] arr = date.Split('/');
                if (dba_info.DATE_FORMAT == "B")
                {
                    int year = Convert.ToInt32(arr[2]);
                    int month = Convert.ToInt32(arr[1]);
                    int day = Convert.ToInt32(arr[0]);
                    return year * 10000 + month * 100 + day;
                }
                else if (dba_info.DATE_FORMAT == "A")
                {
                    int year = Convert.ToInt32(arr[2]);
                    int month = Convert.ToInt32(arr[0]);
                    int day = Convert.ToInt32(arr[1]);
                    return year * 10000 + month * 100 + day;
                }
                else //if (dba_info.DATE_FORMAT == "B")
                {
                    int year = Convert.ToInt32(arr[0]);
                    int month = Convert.ToInt32(arr[1]);
                    int day = Convert.ToInt32(arr[2]);
                    return year * 10000 + month * 100 + day;
                }
            }
            catch { }

            return 19000101;
        }
          public bool TestConnection(string Server, string Username, string Pass, string Database)
        {
            string connectString = CreateConnectString(Server, Username, Pass, Database);
            return _conn.TestConnect(connectString);
        }

        public DataTable GetDataBases(string Server, string UserName, string Pass)
        {
            return _conn.GetDataBases(Server, UserName, Pass);
        }

        public object executeScalar(string sqlString, string connectString)
        {
            return _conn.executeScalar(sqlString, connectString);
        }
        public DataTable executeSelectQuery(string sqlString, string strConnection)
        {
            return _conn.executeSelectQuery(sqlString, strConnection);
        }
        */
        public static bool IsNumber(string text)
        {
            bool res = true;
            try
            {
                if (!string.IsNullOrEmpty(text) && ((text.Length != 1) || (text != "-")))
                {
                    Decimal d = decimal.Parse(text, CultureInfo.CurrentCulture);
                }
            }
            catch
            {
                res = false;
            }
            return res;
        }
        public string CreateConnectString(string Server, string Username, string Pass, string Database)
        {
            string connectString = "Data Source=[SERVER];Initial Catalog=[DATABASE]; uid=[USERNAME];pwd=[PASSWORD];Integrated Security=False;Connect Timeout=500";
            connectString = connectString.Replace("[SERVER]", Server).Replace("[DATABASE]", Database).Replace("[USERNAME]", Username).Replace("[PASSWORD]", Pass);

            return connectString;
        }


        public static string ToRoman(int arabic)
        {
            //string result = "";
            //int[] arabic = new int[] { 1000, 900, 500, 400, 100, 90, 50, 40, 10, 9, 5, 4, 1 };
            //string[] roman = new string[] { "M", "CM", "D", "CD", "C", "XC", "L", "XL", "X", "IX", "V", "IV", "I" };
            //int i = 0;
            //while (n >= arabic[i])
            //{
            //    n = n - arabic[i];
            //    result = result + roman[i];

            //    i = i + 1;
            //}
            //return result;
            /* Arabic Roman relation
            * 1000 = M
            * 900 = CM
            * 500 = D
            *400 = CD
            *100 = C
            *90 = XC
            *50 = L
            *40 = XL
            *10 = L
            *9 = IX
            *5 = V
            *4 = IV
            *1 = 1
            */
            string result = "";
            for (int i = 0; i < arabic; i++)
            {
                while (arabic >= 1000)
                {//check for thousands place

                    result = result + "M";
                    arabic = arabic - 1000;
                }
                while (arabic >= 900)
                {
                    //check for nine hundred place
                    result = result + "CM";
                    arabic = arabic - 900;
                }
                while (arabic >= 500)
                {
                    //check for five hundred place
                    result = result + "D";
                    arabic = arabic - 500;
                }
                while (arabic >= 400)
                {
                    //check for four hundred place
                    result = result + "CD";
                    arabic = arabic - 400;
                }
                while (arabic >= 100)
                {
                    //check for one hundred place
                    result = result + "C";
                    arabic = arabic - 100;
                }
                while (arabic >= 90)
                {
                    //check for ninety place
                    result = result + "XC";
                    arabic = arabic - 90;
                }
                while (arabic >= 50)
                {
                    //check for fifty place
                    result = result + "L";
                    arabic = arabic - 50;
                }
                while (arabic >= 40)
                {
                    // check for forty place
                    result = result + "XL";
                    arabic = arabic - 40;
                }

                while (arabic >= 10)
                {
                    // check for tenth place
                    result = result + "X";
                    arabic = arabic - 10;
                }
                while (arabic >= 9)
                {
                    //check for nineth place
                    result = result + "IX";
                    arabic = arabic - 9;
                }
                while (arabic >= 5)
                {
                    //check for fifth place
                    result = result + "V";
                    arabic = arabic - 5;
                }
                while (arabic >= 4)
                {
                    //check for fourth place
                    result = result + "IV";
                    arabic = arabic - 4;
                }
                while (arabic >= 1)
                {
                    //check for first place
                    result = result + "I";
                    arabic = arabic - 1;
                }
            }
            return result;
        }



        public static DataTable ExeSelectQuery(string query)
        {
            throw new NotImplementedException();
        }

        public static string GetParseExpressionDate(string columnDate, string type)
        {
            string result = "SUBSTRING(CONVERT(" + columnDate + ",System.String),7,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),5,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),1,4)";
            switch (type)
            {
                case "A":
                    result = "SUBSTRING(CONVERT(" + columnDate + ",System.String),5,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),7,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),1,4)";
                    break;
                case "B":
                    result = "SUBSTRING(CONVERT(" + columnDate + ",System.String),7,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),5,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),1,4)";
                    break;
                case "C":
                    result = "SUBSTRING(CONVERT(" + columnDate + ",System.String),1,4)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),5,2)+'/'+SUBSTRING(CONVERT(" + columnDate + ",System.String),7,2)";
                    break;
            }
            return result;
        }

        public DataTable executeSelectQuery(string sqlString, string strConnection)
        {
            return _conn.executeSelectQuery(sqlString, strConnection);
        }



        public DataTable ValidatedDataTransferIn(DataTable dt, string type)
        {
            DataTable dtStruct = new DataTable();
            if (type == "QD")
            {
                BUS.LIST_QDControl control = new LIST_QDControl();

                dtStruct = control.ToTransferInStruct();
            }
            else if (type == "QDADD")
            {
                BUS.LIST_QD_SCHEMAControl control = new LIST_QD_SCHEMAControl();

                dtStruct = control.ToTransferInStruct();
            }
            DataColumn colValidated = new DataColumn("tmp_Validated", typeof(bool));
            dt.Columns.Add(colValidated);
            foreach (DataRow row in dt.Rows)
            {
                bool flag = true;
                foreach (DataColumn col in dtStruct.Columns)
                {
                    if (flag)
                        switch (col.DataType.ToString())
                        {
                            case "System.Decimal":
                                Decimal result;
                                flag = Decimal.TryParse(row[col.ColumnName].ToString(), out result);
                                break;
                            case "System.Int16":
                                Int16 resultInt16;
                                flag = Int16.TryParse(row[col.ColumnName].ToString(), out resultInt16);
                                break;
                            case "System.Int32":
                                Int32 resultInt32;
                                flag = Int32.TryParse(row[col.ColumnName].ToString(), out resultInt32);
                                break;
                            case "System.Int64":
                                Int64 resultInt64;
                                flag = Int64.TryParse(row[col.ColumnName].ToString(), out resultInt64);
                                break;
                            case "System.Boolean":
                                Boolean resultBoolean;
                                flag = Boolean.TryParse(row[col.ColumnName].ToString(), out resultBoolean);
                                break;
                        }
                    else
                        break;
                }
                row["tmp_Validated"] = flag;
            }
            return dt;
        }


       

        public bool TestConnection(string Server, string Username, string Pass, string Database)
        {
            string connectString = CreateConnectString(Server, Username, Pass, Database);
            return _conn.TestConnect(connectString);
        }

        public DataTable GetDataBases(string Server, string UserName, string Pass)
        {
            return _conn.GetDataBases(Server, UserName, Pass);
        }

        public object executeScalar(string sqlString, string connectString)
        {
            return _conn.executeScalar(sqlString, connectString);
        }
       


        public static string GetConnection()
        {
            return Connection.ConnectionString;
        }



        public static void AddLog(string type, string path, string message)
        {
            string erroFile = path + "\\" + type + ".log";
            //System.IO.StreamWriter sw = new System.IO.StreamWriter(erroFile);
            if (!File.Exists(erroFile))
            {
                StreamWriter swt = File.CreateText(erroFile);
                swt.WriteLine(message);
                swt.Close();

            }
            else
            {
                FileStream file = new FileStream(erroFile, FileMode.Append);
                StreamWriter sw = new StreamWriter(file);
                sw.WriteLine(message);
                sw.Close();
                file.Close();
            }
        }
        public static string RemoveAttribute(String input, string attribute)
        {
            int indexS = input.IndexOf(attribute, 0);
            if (indexS != -1)
            {
                int indexE = input.IndexOf(";", indexS + attribute.Length);
                if (indexE == -1 || indexE == input.Length - 1)
                    return input.Substring(0, indexS);
                return input.Substring(0, indexS) + input.Substring(indexE + 1);
            }
            else
                return input;
        }
        public static string HiddenAttribute(String input, string attribute)
        {
            int indexS = input.IndexOf(attribute, 0);
            if (indexS != -1)
            {
                int indexE = input.IndexOf(";", indexS + attribute.Length);
                if (indexE == -1 || indexE == input.Length - 1)
                    return attribute + "=******;" + input.Substring(0, indexS);
                return input.Substring(0, indexS) + attribute + "=*****;" + input.Substring(indexE + 1);
            }
            else
                return attribute + "=*****;" + input;
        }
    }
}
